package com.maisonliberte.dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.maisonliberte.conf.Config;
import com.maisonliberte.entity.ProductInfo;
import com.maisonliberte.entity.Staff;
import com.maisonliberte.entity.StaffInfo;
import com.maisonliberte.entity.TableInfo;
import com.maisonliberte.enums.ImageSizeEnum;
import com.maisonliberte.util.FileUtil;
import com.maisonliberte.util.StringUtil;

public class Dao {
	
	private static Dao dao;
	
	private Dao(){
		
	}
	
	public static Dao getInstance(){
		if(dao == null){
			dao = new Dao();
		}
		return dao;
	}
	
	public int getNewProductNumber(){
		
		return 1000;
	}
	
	private Connection getConnection() throws SQLException{
		try{
			return DriverManager.getConnection(Config.getInstance().jdbcUrl, Config.getInstance().jdbcId, Config.getInstance().jdbcPwd);
		}catch(SQLException e){
			throw e;
		}
	}
	
	public void update(TableInfo tInfo){
		String sql = "update mall_goods set price = {$price}, consumer_price = {$consumer_price}, price_ment = '{$price_ment}', s_qty = 4 where uid = {$uid}";
		String sql_qty_zero = "update mall_goods set s_qty = 2 where uid = {$uid}";

		String errSql = "";
		
		Connection conn;
        Statement stat;
		try {

            conn = getConnection();
            conn.setAutoCommit(false);
            
            try{
           	 Class.forName("com.mysql.jdbc.Driver");
            }catch(ClassNotFoundException e){
           	 e.printStackTrace();
            }
            
            try{
            
           	 stat = conn.createStatement();
       		 String tmpSql = "";
       		 
       		if(tInfo.getQty() > 0){
       			tmpSql = sql;
          		tmpSql = tmpSql.replace("{$uid}", tInfo.getUid());
           		tmpSql = tmpSql.replace("{$price}", String.valueOf(tInfo.getConsumerPrice()));
           		tmpSql = tmpSql.replace("{$consumer_price}", "0");
           		tmpSql = tmpSql.replace("{$price_ment}", tInfo.getPrice_ment().isEmpty() ? "" : String.valueOf(tInfo.getPrice_ment()));
       		}else{
       			tmpSql = sql_qty_zero;
       			tmpSql = tmpSql.replace("{$uid}", tInfo.getUid());
       		}		 
       		 //'','','','','','','','','','','','','','{$}','{$}','{$}','{$}','{$}','{$}','{$}','{$}','{$}','{$}','{$}','{$}','{$}','{$}','{$}','{$}','{$}','{$}','{$}','{$}', '{$}' ,'{$}','{$}'
       		 errSql = tmpSql;
       		 stat.execute(tmpSql);
       		
           	 conn.commit();
           	 System.out.println("COMMIT");
            
            } catch(SQLException ex) {
            	System.out.println("SQLException: " + ex.getMessage());
                System.out.println("SQLState: " + ex.getSQLState());
                System.out.println("VendorError: " + ex.getErrorCode());
                System.out.println("errSQL : " + errSql);
                conn.rollback();
            }
            catch(Exception ex){
            	System.out.println("Exception: " + ex.getMessage());
            	System.out.println("errSQL : " + errSql);
            	conn.rollback();
            }
            finally {
           	 conn.close();
            } 
            
       } catch (Exception ex) {
            System.out.println("Exception: " + ex.getMessage());
       }
		
		
		
	}
	
	public List<TableInfo> selectProductSource(){
		Connection conn;
        Statement stat;
        String sql = "select uid, src_url, weight from mall_goods where src_url is not null";
        List<TableInfo> result = new ArrayList<TableInfo>();
        try {

            conn = getConnection();
            
            try{
           	 Class.forName("com.mysql.jdbc.Driver");
            }catch(ClassNotFoundException e){
           	 e.printStackTrace();
            }
            
            try{
            
           	 	stat = conn.createStatement();
           	 	ResultSet rs = stat.executeQuery(sql);
           	 	while(rs.next()){
           	 		String uid = rs.getString("uid");
           	 		String src_url = rs.getString("src_url");
           	 		String weight = rs.getString("weight");
           	 		TableInfo tInfo = new TableInfo();
           	 		tInfo.setSourceUrl(src_url);
           	 		tInfo.setUid(uid);
           	 		tInfo.setWeight(Integer.valueOf(weight));
           	 		result.add(tInfo);
           	 	}
           	 	
            } catch(SQLException ex) {
            	System.out.println("SQLException: " + ex.getMessage());
                System.out.println("SQLState: " + ex.getSQLState());
                System.out.println("VendorError: " + ex.getErrorCode());
                conn.rollback();
            }
            catch(Exception ex){
            	System.out.println("Exception: " + ex.getMessage());
            	conn.rollback();
            }
            finally {
           	 conn.close();
            } 
            
       } catch (Exception ex) {
            System.out.println("Exception: " + ex.getMessage());
       }
        
        return result;
	}
	
	
	public int selectProductNumber(String buyer){
		Connection conn;
        Statement stat;
        String sql = "select ifnull(max(number+0)+1, 1000) from mall_goods where vendor = '" + buyer + "'";
        int number = 0;
        try {

            conn = getConnection();
            
            try{
           	 Class.forName("com.mysql.jdbc.Driver");
            }catch(ClassNotFoundException e){
           	 e.printStackTrace();
            }
            
            try{
            
           	 	stat = conn.createStatement();
           	 	ResultSet rs = stat.executeQuery(sql);
           	 	rs.next();
           	 	number = rs.getInt(1); 
            
            } catch(SQLException ex) {
            	System.out.println("SQLException: " + ex.getMessage());
                System.out.println("SQLState: " + ex.getSQLState());
                System.out.println("VendorError: " + ex.getErrorCode());
                conn.rollback();
            }
            catch(Exception ex){
            	System.out.println("Exception: " + ex.getMessage());
            	conn.rollback();
            }
            finally {
           	 conn.close();
            } 
            
       } catch (Exception ex) {
            System.out.println("Exception: " + ex.getMessage());
       }
        
        return number;
	}
	
	public void insertBrands(List<Staff> list){
		Connection conn;
        Statement stat;
        String sql_maker = "insert into mall_maker (id, name, pic, comment) values ('$id', '$name', '$pic', '$comment')";
        String sql_staff = "insert into mall_staff (id, name, pic, comment) values ('$id', '$name', '$pic', '$comment')";
        String sql_brand = "insert into mall_brand (name, tag, img2, code_use, code, img_external, maker_id) values ('$name', '$tag', '$pic', 'Y', '$comment', 'Y', '$id')";
        
        try {

            conn = getConnection();
            conn.setAutoCommit(false);
            
            try{
           	 Class.forName("com.mysql.jdbc.Driver");
            }catch(ClassNotFoundException e){
           	 e.printStackTrace();
            }
            
            try{
            
           	 stat = conn.createStatement();
           	 for(Staff staff : list){
           		 
           		 if(staff.getPic_tar().isEmpty()) continue;
           		 
           		 if(staff instanceof StaffInfo){
           			
           			String tmp = sql_staff;
           			tmp = tmp.replace("$id", staff.getId());
           			tmp = tmp.replace("$name", StringUtil.replaceNGString(staff.getName()));
           			tmp = tmp.replace("$pic", String.format("%s%s", Config.getInstance().httpImageUrl, staff.getPic_tar().replace("/public_html", "")));
           			tmp = tmp.replace("$comment", StringUtil.replaceNGString(staff.getComment()));
           			stat.addBatch(tmp); 
           			 
           		 }else{
           			 
           			String tmp = sql_maker;
           			tmp = tmp.replace("$id", staff.getId());
           			tmp = tmp.replace("$name", StringUtil.replaceNGString(staff.getName()));
           			tmp = tmp.replace("$pic", String.format("%s%s", Config.getInstance().httpImageUrl, staff.getPic_tar().replace("/public_html", "")));
           			tmp = tmp.replace("$comment", StringUtil.replaceNGString(staff.getComment()));
           			stat.addBatch(tmp);
           			
           			tmp = sql_brand;
           			//String sql_brand = "insert into mall_brand (name, tag, img2, coce_use, code, img_external, make_id) values ('$name', '$tag', '$pic', 'Y', '$comment', 'Y', '$id')";
           			tmp = tmp.replace("$name", StringUtil.replaceNGString(staff.getBrand().isEmpty() ? staff.getName() : staff.getBrand()));
           			tmp = tmp.replace("$tag", StringUtil.replaceNGString(staff.getBrand().isEmpty() ? staff.getName() : staff.getBrand()));
           			tmp = tmp.replace("$pic", String.format("%s%s", Config.getInstance().httpImageUrl, staff.getPic_tar().replace("/public_html", "")));
           			tmp = tmp.replace("$comment", StringUtil.replaceNGString(staff.getComment()));
           			tmp = tmp.replace("$id", StringUtil.replaceNGString(staff.getId()));
           			stat.addBatch(tmp);
           		 }
           		 
           		 System.out.println(staff.getName() + " INSERT COMPLETED");
           		 
           	 }
           	 
           	 stat.executeBatch();
           	 conn.commit();
           	 System.out.println("COMMIT");
            
            } catch(SQLException ex) {
            	System.out.println("SQLException: " + ex.getMessage());
                System.out.println("SQLState: " + ex.getSQLState());
                System.out.println("VendorError: " + ex.getErrorCode());
                conn.rollback();
            }
            catch(Exception ex){
            	System.out.println("Exception: " + ex.getMessage());
            	conn.rollback();
            }
            finally {
           	 conn.close();
            } 
            
       } catch (Exception ex) {
            System.out.println("Exception: " + ex.getMessage());
       }
	}
	
	public void insertProducts(List<ProductInfo> list){
		
		if(list.size() == 0) return;
		System.out.println("insert target count :" + list.size());
		
		StringBuilder sb = new StringBuilder();
		
		Connection conn;
        Statement stat;
        //String sql_product = "INSERT INTO mall_goods (uid,cate,mcate,number,brand,special,name,search_name,add_msg,model,comp,made,price,consumer_price,price_ment,unit,def_qty,s_qty,qty,reserve,carriage,op_goods_type,op_goods,display,icon,image1,image2,image3,image4,image5,other_image,explan,tag,o_num1,o_num2,o_num3,sequence,coop_sdate,coop_edate,coop_close,coop_pay,p_id,signdate) ";
        //sql_product += ""VALUES ('','100100100000','','1000','1','','project name','projectname','','model name','maker','country','10000','20000','telephone','ｰｳ','1','4','6','3|30','3|30000','B|1|3|4','','2|2','|icon_best.gif|icon_hit.gif|icon_new.gif','1/1001001000001000.gif','2/1001001000001000.gif','3/1001001000001000.gif','4/1001001000001000.gif','5/1001001000001000.gif','','detail info',',YU NAKAGAWA,','1', '1' ,'1','3','','','','','admin','1356925689')
		
		//";
        
        String sql_product = "INSERT INTO mall_goods (cate,mcate,number,brand,special,name,search_name,add_msg,model,comp,made,price,consumer_price,price_ment,unit,def_qty,s_qty,qty,reserve,carriage,op_goods_type,op_goods,display,icon,image1,image2,image3,image4,image5,other_image,explan,tag,o_num1,o_num2,o_num3,sequence, p_id, weight, img_external, signdate, detail_image, visible, src_url, isforwardFee, type, auth_ck, vendor, vendor_disp, vendor_o_num, v_cnt, o_cnt, c_cnt)"; 
        sql_product += " VALUES ('{$cate_num}','{$mcate}','{$number}','{$brand}','{$special}','{$name}','{$search_name}','{$add_msg}','{$model}','{$comp}','{$made}','{$price}','{$consumer_price}','{$price_ment}','{$unit}','{$def_qty}','{$s_qty}','{$qty}','{$reserve}','{$carriage}','{$op_goods_type}','{$op_goods}','{$display}','{$icon}','{$image1}','{$image2}','{$image3}','{$image4}','{$image5}','{$other_image}','{$explan}','{$tag}','{$val_add1}', '{$val_add2}' ,'{$val_add3}','{$sequence}','{$my_id}','{$weight}','{$img_external}','{$signdate}', '{$detail_image}', 'N', '{$srcUrl}', '{$isforwardFee}', ";
        sql_product += " 'C', 'N', '${vendor}', 0, 0, 0, 0, 0);";  		
        
        String sql_other = "INSERT INTO mall_goods_otherinfo(uid, s_long, s_function, s_design, s_echo, s_tradition, s_rare, staff_id, staff_comment, maker_id, maker_comment, brand_id)";
        sql_other += " values('$uid', '$s_long', '$s_function', '$s_design', '$s_echo', '$s_tradition', '$s_rare', '$staff_id', '$staff_comment', '$maker_id', '$maker_comment', '$brand_id');";
        
        String sql_requireInfo = "INSERT INTO mall_goods_info(guid, name1, content1, o_num, name2, content2) values($guid, '$name1', '$content1', '$o_num', 'x', 'x')";		
        String errSql = "";
        try {

            conn = getConnection();
            conn.setAutoCommit(false);
            
            try{
           	 Class.forName("com.mysql.jdbc.Driver");
            }catch(ClassNotFoundException e){
           	 e.printStackTrace();
            }
            
            try{
            
           	 stat = conn.createStatement();
           	 for(ProductInfo pInfo : list){
           		 TableInfo table = pInfo.getTableInfo();
           		
           		 if(pInfo.getNonDetailImageUrl(ImageSizeEnum.LARGE_500px) == null || pInfo.getNonDetailImageUrl(ImageSizeEnum.LARGE_500px).isEmpty()) continue;
           		 
           		 String tmpSql = sql_product;
           		//'','','','','','','','','','','','','','{$}','{$}','{$}','{$}','{$}','{$}','{$}','{$}','{$}','{$}','{$}','{$}','{$}','{$}','{$}','{$}','{$}','{$}','{$}','{$}', '{$}' ,'{$}','{$}'
           		tmpSql = tmpSql.replace("{$cate_num}", table.getCate());
           		tmpSql = tmpSql.replace("{$mcate}", "");
           		tmpSql = tmpSql.replace("{$number}", String.valueOf(table.getNumber()));
           		tmpSql = tmpSql.replace("{$brand}", String.valueOf(table.getBrand()));
           		tmpSql = tmpSql.replace("{$special}", table.getSpecial());
           		tmpSql = tmpSql.replace("{$name}", table.getName().replace("'", "''"));
           		tmpSql = tmpSql.replace("{$search_name}", table.getSearchName());
           		tmpSql = tmpSql.replace("{$add_msg}", table.getAddMsg());
           		tmpSql = tmpSql.replace("{$model}", table.getModel());
           		tmpSql = tmpSql.replace("{$comp}", table.getComp());
           		tmpSql = tmpSql.replace("{$made}", table.getMade());
           		tmpSql = tmpSql.replace("{$price}", String.valueOf(table.getConsumerPrice()));
           		tmpSql = tmpSql.replace("{$consumer_price}", "0");
           		//tmpSql = tmpSql.replace("{$consumer_price}", String.valueOf(table.getPrice()));
           		tmpSql = tmpSql.replace("{$price_ment}", String.valueOf(table.getPrice_ment()));
           		tmpSql = tmpSql.replace("{$unit}", String.valueOf(table.getUnit()));
           		tmpSql = tmpSql.replace("{$def_qty}", String.valueOf(table.getDefQty()));
           		tmpSql = tmpSql.replace("{$s_qty}", String.valueOf(table.getsQty()));
           		tmpSql = tmpSql.replace("{$qty}", String.valueOf(table.getQty()));
           		tmpSql = tmpSql.replace("{$reserve}", String.valueOf(table.getQty()));
           		tmpSql = tmpSql.replace("{$carriage}", String.valueOf(table.getCarriage()));
           		tmpSql = tmpSql.replace("{$op_goods_type}", String.valueOf(table.getOpGoodsType()));
           		tmpSql = tmpSql.replace("{$op_goods}", String.valueOf(table.getOpGoods()));
           		tmpSql = tmpSql.replace("{$display}", String.valueOf(table.getDisplay()));
           		tmpSql = tmpSql.replace("{$icon}", String.valueOf(table.getIcon()));
           		tmpSql = tmpSql.replace("{$image1}", pInfo.getNonDetailImageUrl(ImageSizeEnum.LARGE_500px));
           		tmpSql = tmpSql.replace("{$image2}", pInfo.getNonDetailImageUrl(ImageSizeEnum.LARGE_500px));
           		//tmpSql = tmpSql.replace("{$image2}", pInfo.getNonDetailImageUrl(ImageSizeEnum.MIDDLE_250px));
           		tmpSql = tmpSql.replace("{$image3}", pInfo.getNonDetailImageUrl(ImageSizeEnum.LIST_150px));
           		tmpSql = tmpSql.replace("{$image4}", pInfo.getNonDetailImageUrl(ImageSizeEnum.LARGE_500px));
           		//tmpSql = tmpSql.replace("{$image4}", pInfo.getNonDetailImageUrl(ImageSizeEnum.DISPLAY_120px));
           		tmpSql = tmpSql.replace("{$image5}", pInfo.getNonDetailImageUrl(ImageSizeEnum.SMALL_80px));
           		tmpSql = tmpSql.replace("{$other_image}", String.valueOf(pInfo.getOtherImage()));
           		tmpSql = tmpSql.replace("{$detail_image}", String.valueOf(pInfo.getDetailImage()));
           		tmpSql = tmpSql.replace("{$explan}", String.valueOf(pInfo.getContent()));
           		tmpSql = tmpSql.replace("{$tag}", String.valueOf(table.getTag()));
           		tmpSql = tmpSql.replace("{$val_add1}", "1");
           		tmpSql = tmpSql.replace("{$val_add2}", "1");
           		tmpSql = tmpSql.replace("{$val_add3}", "1");
           		tmpSql = tmpSql.replace("{$sequence}", "3");
           		tmpSql = tmpSql.replace("{$coop_sdate}", "");
           		tmpSql = tmpSql.replace("{$coop_edate}", "");
           		tmpSql = tmpSql.replace("{$coop_close}", "");
           		tmpSql = tmpSql.replace("{$coop_pay}", "");
           		tmpSql = tmpSql.replace("{$my_id}", "YWRtaW4=");
           		tmpSql = tmpSql.replace("{$weight}", String.valueOf(table.getWeight()));
           		tmpSql = tmpSql.replace("{$img_external}", "Y");
           		tmpSql = tmpSql.replace("{$signdate}", String.valueOf(System.currentTimeMillis() / 1000L ));
           		tmpSql = tmpSql.replace("{$srcUrl}", String.valueOf(table.getSourceUrl()));
           		tmpSql = tmpSql.replace("{$isforwardFee}", String.valueOf(table.isForwardFee() ? "Y" : "N"));
           		tmpSql = tmpSql.replace("${vendor}", pInfo.getBuyer());
           		errSql = tmpSql;
           		stat.execute(tmpSql);    //.addBatch(tmpSql);
           		sb.append(String.format("%s\n", tmpSql));
           		int guid = -1;
           		ResultSet rsset = stat.getGeneratedKeys();
           		if(rsset.next()){
           			guid = rsset.getInt(1);
           		}
           		
           		// mall_goods_info
           		int idx = 1;
           		for(String key : pInfo.getSpec().keySet()){
           			tmpSql = sql_requireInfo;
           			tmpSql = tmpSql.replace("$guid", String.valueOf(guid));

           			if(pInfo.getSpec().get(key) != null && !pInfo.getSpec().get(key).trim().isEmpty()){
               			tmpSql = tmpSql.replace("$name1", key.trim());
               			tmpSql = tmpSql.replace("$content1", pInfo.getSpec().get(key).trim());
           			}else if(key != null && !key.trim().isEmpty()){
               			tmpSql = tmpSql.replace("$name1", "");
               			tmpSql = tmpSql.replace("$content1", key.trim());
           			}
           			
           			tmpSql = tmpSql.replace("$o_num", String.valueOf(idx));
           			idx = idx + 1;
           			
           			stat.execute(tmpSql);    //.addBatch(tmpSql);
               		sb.append(String.format("%s\n", tmpSql));
           			
           		}
           		
           		tmpSql = sql_requireInfo;
       			tmpSql = tmpSql.replace("$guid", String.valueOf(guid));
       			tmpSql = tmpSql.replace("$name1", "구매 지역");
       			tmpSql = tmpSql.replace("$content1", "일본");
       			tmpSql = tmpSql.replace("$o_num", String.valueOf(idx+1));
       			stat.execute(tmpSql);    //.addBatch(tmpSql);
           		sb.append(String.format("%s\n", tmpSql));
           		
           		tmpSql = sql_requireInfo;
       			tmpSql = tmpSql.replace("$guid", String.valueOf(guid));
       			tmpSql = tmpSql.replace("$name1", "배송 방법");
       			tmpSql = tmpSql.replace("$content1", "해외직배송(현지에서 직접 구매 및 배송을 전담하기 때문에 최대 14일 정도 소요될 수 있습니다.)");
       			tmpSql = tmpSql.replace("$o_num", String.valueOf(idx+2));
       			stat.execute(tmpSql);    //.addBatch(tmpSql);
           		sb.append(String.format("%s\n", tmpSql));
           		
        		if(pInfo.getPriceInfo().ConPrice() > 150000){
        			tmpSql = sql_requireInfo;
           			tmpSql = tmpSql.replace("$guid", String.valueOf(guid));
           			tmpSql = tmpSql.replace("$name1", "통부관세");
           			tmpSql = tmpSql.replace("$content1", "본 제품은 배송비를 포함하여 15만원을 초과하기에 통부관세 부과 대상입니다.");
           			tmpSql = tmpSql.replace("$o_num", String.valueOf(idx+3));
           			stat.execute(tmpSql);    //.addBatch(tmpSql);
               		sb.append(String.format("%s\n", tmpSql));
               		
        			tmpSql = sql_requireInfo;
           			tmpSql = tmpSql.replace("$guid", String.valueOf(guid));
           			tmpSql = tmpSql.replace("$name1", "");
           			tmpSql = tmpSql.replace("$content1", "통부관세는 경우에 따라 부과 대상에서 제외될 수도 있습니다.");
           			tmpSql = tmpSql.replace("$o_num", String.valueOf(idx+4));
           			stat.execute(tmpSql);    //.addBatch(tmpSql);
               		sb.append(String.format("%s\n", tmpSql));
        		}
           		
//           		 
//           		tmpSql = sql_other;
//           		//" values('$number', '$s_long', '$s_function', '$s_design', '$s_echo', '$s_tradition', '$s_rare', '$staff_id', '$staff_comment', '$maker_id', '$maker_comment', '$brand_id')";
//           		tmpSql = tmpSql.replace("$uid", String.valueOf(guid));
//           		tmpSql = tmpSql.replace("$s_long", String.valueOf(pInfo.getTableOther().getS_long()));
//           		tmpSql = tmpSql.replace("$s_function", String.valueOf(pInfo.getTableOther().getS_function()));
//           		tmpSql = tmpSql.replace("$s_design", String.valueOf(pInfo.getTableOther().getS_design()));
//           		tmpSql = tmpSql.replace("$s_echo", String.valueOf(pInfo.getTableOther().getS_echo()));
//           		tmpSql = tmpSql.replace("$s_tradition", String.valueOf(pInfo.getTableOther().getS_tradition()));
//           		tmpSql = tmpSql.replace("$s_rare", String.valueOf(pInfo.getTableOther().getS_rare()));
//           		tmpSql = tmpSql.replace("$staff_id", String.valueOf(pInfo.getTableOther().getStaff_id()));
//           		tmpSql = tmpSql.replace("$staff_comment", String.valueOf(pInfo.getTableOther().getStaff_comment()));
//           		tmpSql = tmpSql.replace("$maker_id", String.valueOf(pInfo.getTableOther().getMaker_id()));
//           		tmpSql = tmpSql.replace("$maker_comment", String.valueOf(pInfo.getTableOther().getMaker_comment()));
//           		tmpSql = tmpSql.replace("$brand_id", String.valueOf(pInfo.getTableOther().getBrand_id()));
//           		
//    
//           		stat.execute(tmpSql);
//           		sb.append(String.format("%s\n", tmpSql));
           	 }
           	 
           	 FileUtil.writeFile(sb.toString(), Config.getInstance().sqlfile);
           	 
           	 //stat.executeBatch();
           	 conn.commit();
           	 System.out.println("COMMIT");
            
            } catch(SQLException ex) {
            	System.out.println("SQLException: " + ex.getMessage());
                System.out.println("SQLState: " + ex.getSQLState());
                System.out.println("VendorError: " + ex.getErrorCode());
                System.out.println("errSQL : " + errSql);
                conn.rollback();
            }
            catch(Exception ex){
            	System.out.println("Exception: " + ex.getMessage());
            	System.out.println("errSQL : " + errSql);
            	conn.rollback();
            }
            finally {
           	 conn.close();
            } 
            
       } catch (Exception ex) {
            System.out.println("Exception: " + ex.getMessage());
       }
	}
	
	public boolean isDuplicateProduct(String url){
		
		Connection conn;
        Statement stat;
        String sql = "select count(uid) from mall_goods where src_url = '?'";
        int number = 0;
        try {

            conn = getConnection();
            
            try{
           	 Class.forName("com.mysql.jdbc.Driver");
            }catch(ClassNotFoundException e){
           	 e.printStackTrace();
            }
            
            try{
            
           	 	stat = conn.createStatement();
           	 	ResultSet rs = stat.executeQuery(sql.replace("?", url.trim()));
           	 	rs.next();
           	 	number = rs.getInt(1); 
            
            } catch(SQLException ex) {
            	System.out.println("isDuplicateProduct Exception " + sql + ", " + url);
            	System.out.println("SQLException: " + ex.getMessage());
                System.out.println("SQLState: " + ex.getSQLState());
                System.out.println("VendorError: " + ex.getErrorCode());
                //conn.rollback();
            }
            catch(Exception ex){
            	System.out.println("Exception: " + ex.getMessage());
            	//conn.rollback();
            }
            finally {
           	 conn.close();
            } 
            
       } catch (Exception ex) {
            System.out.println("Exception: " + ex.getMessage());
       }
        
        return number > 0;
		
	}
	
	
}
